﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySkiPatrolEntity;

namespace MySkiPatrolSQL
{
    public static class SignInSQL
    {
        #region Admin - Daily Signin
        public static string GetEmployeeIDInfo(ScanResponse data)
        {
            StringBuilder sql = new StringBuilder();
            //sql.Append("select a.memberid, a.firstname, a.lastname ");
            //sql.Append("from " + data.DataTransaction.PatrolPrefix + "_member a ");
            //sql.Append("where a.IdCardString = '" + data.MemberID + "' ");

            sql.Append("select * from " + data.DataTransaction.PatrolPrefix + "_Member where MemberID = (select MemberID from " + data.DataTransaction.PatrolPrefix + "_MemberIdCards where ID = '" + data.IDCardString + "') ");
            return sql.ToString();
        }

        public static string AssignIdToMember(ScanResponse data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into " + data.DataTransaction.PatrolPrefix + "_MemberIdCards (id,memberid) values ('" + data.IDCardString + "','" + data.MemberID + "') ");
            //sql.Append("Update patrollerinfo set employeeid = '" + data.EmployeeID + "' where personid = '" + data.PersonID + "' ");
            return sql.ToString();
        }

        public static string GetEmployeeInfo(ScanResponse data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + data.DataTransaction.PatrolPrefix + "_Member where MemberID = (select MemberID from " + data.DataTransaction.PatrolPrefix + "_MemberIdCards where ID = '" + data.IDCardString + "') ");
            return sql.ToString();
        }

        public static string GetEmployeeDailySignin(ScanResponse data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + data.DataTransaction.PatrolPrefix + "_dailysignin ");
            sql.Append("where memberid = '" + data.MemberID + "' ");
            sql.Append("and signout is null ");
            return sql.ToString();
        }

        public static string EmployeeDailySignin(ScanResponse data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into " + data.DataTransaction.PatrolPrefix + "_dailysignin (memberid,signin) ");
            sql.Append("values ('" + data.MemberID + "','" + data.SignIn + "') ");
            return sql.ToString();
        }

        public static string SetIDCardLastUsedDate(ScanResponse data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + data.DataTransaction.PatrolPrefix + "_memberidcards set lastused = '' where id = '" + data.IDCardString + "' and memberid = '" + data.MemberID + "' ");
            return sql.ToString();
        }

        public static string EmployeeSignIn(EmployeeSignIn data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into " + data.DataTransaction.PatrolPrefix + "_servicedays (memberid,signin,signout) ");
            sql.Append("values ('" + data.MemberID + "','" + data.SignIn + "', '" + data.SignOut + "')");
            return sql.ToString();
        }

        public static string GetDailySignins(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select a.memberid, b.firstname, b.lastname, a.signout, a.signin ");
            sql.Append("from " + data.dbPrefix + "_dailysignin a ");
            sql.Append("join " + data.dbPrefix + "_member b on b.memberid = a.memberid ");
            sql.Append("where signout is null ");
            return sql.ToString();
        }

        public static string UpdateEmployeeAssignment(ScanResponse data)
        {
            StringBuilder sql = new StringBuilder();
            //sql.Append("update dailysignin set ");
            //sql.Append("radio = '" + data.Radio + "', ");
            //sql.Append("assignment = '" + data.Assignment + "' ");
            //sql.Append("where personid = '" + data.PersonID + "' ");
            return sql.ToString();
        }

        //public static string CloseDailySignins()
        //{
        //    StringBuilder sql = new StringBuilder();
        //    sql.Append("update dailysignin set signout = getdate() where signout is null ");
        //    return sql.ToString();
        //}

        public static string GetEmployeesWithNoID(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select a.memberid, a.lastname, a.firstname ");
            sql.Append("from " + data.dbPrefix + "_member a  ");
            sql.Append("where a.id is null or a.id = '' ");
            sql.Append("order by a.lastname, a.firstname ");
            return sql.ToString();
        }

        public static string GetAllMembers(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select a.memberid, a.lastname, a.firstname from bss_member a order by a.lastname, a.firstname ");
            return sql.ToString();
        }

        public static string AssignIdToEmployee(ScanResponse data)
        {
            StringBuilder sql = new StringBuilder();
            //sql.Append("Update patrollerinfo set memberid = '" + data.memberid + "' where personid = '" + data.PersonID + "' ");
            return sql.ToString();
        }

        public static string CancelSignin(ScanResponse data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + data.DataTransaction.PatrolPrefix + "_dailysignin where memberid = '" + data.MemberID + "' and signout is null ");
            return sql.ToString();
        }
        #endregion
    }
}
